package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.common.Constants;
import com.sq.partner.manager.dao.IGatewayRoleDao;
import com.sq.partner.manager.mybatis.model.GatewayRole;
import com.sq.partner.manager.mybatis.model.GatewayRoleMenu;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.GatewayUserRole;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;

@Repository
public class GatewayRoleDaoImpl extends BaseDaoImpl implements IGatewayRoleDao {
	private static Logger logger = LogUtil.getLog(BaseDaoImpl.class);
	@Override
	public Page queryPage(Page page, Integer pageIndex, GatewayRole role,GatewayUser user)
			throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		List<String> list=new ArrayList<String>();
		Page p = null;
		List lists = null;
		try{
			if(StrUtil.isNotEmpty(user.getDepartmentStr())){
				conn = this.getConnection();
				StringBuffer sqlbuf=new StringBuffer();
				sqlbuf.append(" SELECT T.ROLEID,T.ROLE_NAME,T.OPER_USER,TO_CHAR(T.OPER_TIME, 'yyyy-MM-dd HH24:MI:SS') AS OPER_TIME,"
						+ " T.STATUS,U.USER_NAME,K.DEPT_CODE,L.DEPT_CNAME FROM T_GATEWAY_ROLE T INNER JOIN T_GATEWAY"
						+ "_ROLE_DEPT K  ON T.ROLEID = K.ROLE_ID INNER JOIN T_CORE_DEPT L ON K.DEPT_CODE = L.DEPT_CODE "
						+ " LEFT JOIN T_GATEWAY_USER U ON T.OPER_USER = U.USERID WHERE 1 = 1");
				if(!(user.getDepartmentStr().contains(Constants.HQ_BRANCH_CODE))){
					sqlbuf.append(" AND K.DEPT_CODE IN (SELECT DISTINCT TT.DEPT_CODE FROM "
							+ "T_CORE_DEPT TT START WITH TT.DEPT_CODE IN ("+user.getDepartmentStr()+") CONNECT BY NOCYCLE TT.SNR_DEPT = PRIOR TT.DEPT_CODE)");
				}
				String sql;
				if(null!=role.getRoleName()&&StrUtil.isNotEmpty(role.getRoleName())){
					sqlbuf.append(" and t.role_name like ? ");
					list.add("%"+role.getRoleName()+"%");
				}
				sqlbuf.append(" order by t.oper_time desc");
				sql=sqlbuf.toString();
				logger.debug("执行SQL：" + sql);


				pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
				for (int i = 0; i < list.size(); i++) {
					pstmt.setObject(i+1, list.get(i));
				}
				rs = pstmt.executeQuery();
				rs.next();
				Long total = rs.getLong(1);

				//查出分页
				p = new Page(page.getCurrent(),total);
				SqlUtil sqlUtil=new	SqlUtil();
				String pagesql=sqlUtil.buildPageSqlForOracle(sql,p).toString();
				logger.debug("执行SQL："+pagesql);
				pstmt1 = conn.prepareStatement(pagesql);
				for (int i = 0; i < list.size(); i++) {
					pstmt1.setObject(i+1, list.get(i));
				}
				rs = pstmt1.executeQuery();
				lists = DBUtil.transRs2List(rs);
			}
			p.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return p;
	}
	
	
	@Override
	public boolean deleteRole(GatewayRole role) throws Exception {
		return this.delete("delGatewayRoleById", role);
	}


	@Override
	public boolean addRole(GatewayRole role) throws Exception {
		return this.add("insertGatewayRole", role);
	}


	@Override
	public GatewayRole queryRoleNameExistsOrNot(Map<String, String> map)
			throws Exception {
		return (GatewayRole) this.query("queryGatewayRoleByMap", map);
	}


	@Override
	public boolean modifyRole(GatewayRole role) throws Exception {
		return this.update("updateGatewayRoleNotNull", role);
	}


	@Override
	public boolean delUserRoleRoleid(GatewayUserRole userRole) throws Exception {
		return this.delete("delUserRoleRoleid", userRole);
		
	}


	@Override
	public Long addRoleAndReturnKey(GatewayRole role) throws Exception {
		this.addAndReturnPrimaryKey("insertGatewayRole", role);
		return role.getRoleid();
	}

}
